USE [RockBusDb]
GO
ALTER TABLE [dbo].[QueueEntry] DROP CONSTRAINT [FK_QueueEntry_MessageProcessingState1]
GO
ALTER TABLE [dbo].[QueueEntry] DROP CONSTRAINT [FK_QueueEntry_Message]
GO
ALTER TABLE [dbo].[MessageFragment] DROP CONSTRAINT [FK_MessageFragment_Message]
GO
ALTER TABLE [dbo].[ErrorMessage] DROP CONSTRAINT [FK_ErrorMessage_Message]
GO
/****** Object:  Index [IX_QueueEntry_MessageId]    Script Date: 27-4-2014 16:45:28 ******/
DROP INDEX [IX_QueueEntry_MessageId] ON [dbo].[QueueEntry]
GO
/****** Object:  Index [IX_QueueEntry_BatchId]    Script Date: 27-4-2014 16:45:28 ******/
DROP INDEX [IX_QueueEntry_BatchId] ON [dbo].[QueueEntry]
GO
/****** Object:  Index [IX_MessageFragment_MessageId]    Script Date: 27-4-2014 16:45:28 ******/
DROP INDEX [IX_MessageFragment_MessageId] ON [dbo].[MessageFragment]
GO
/****** Object:  Table [dbo].[QueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP TABLE [dbo].[QueueEntry]
GO
/****** Object:  Table [dbo].[MessageProcessingState]    Script Date: 27-4-2014 16:45:28 ******/
DROP TABLE [dbo].[MessageProcessingState]
GO
/****** Object:  Table [dbo].[MessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
DROP TABLE [dbo].[MessageFragment]
GO
/****** Object:  Table [dbo].[Message]    Script Date: 27-4-2014 16:45:28 ******/
DROP TABLE [dbo].[Message]
GO
/****** Object:  Table [dbo].[ErrorMessage]    Script Date: 27-4-2014 16:45:28 ******/
DROP TABLE [dbo].[ErrorMessage]
GO
/****** Object:  StoredProcedure [dbo].[RollbackEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[RollbackEntry]
GO
/****** Object:  StoredProcedure [dbo].[GetMessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[GetMessageFragment]
GO
/****** Object:  StoredProcedure [dbo].[FaultEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[FaultEntry]
GO
/****** Object:  StoredProcedure [dbo].[EnqueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[EnqueueEntry]
GO
/****** Object:  StoredProcedure [dbo].[DequeueEntries]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[DequeueEntries]
GO
/****** Object:  StoredProcedure [dbo].[DeleteMessage]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[DeleteMessage]
GO
/****** Object:  StoredProcedure [dbo].[CreateMessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CreateMessageFragment]
GO
/****** Object:  StoredProcedure [dbo].[CreateMessage]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CreateMessage]
GO
/****** Object:  StoredProcedure [dbo].[CreateErrorMessage]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CreateErrorMessage]
GO
/****** Object:  StoredProcedure [dbo].[CompleteMessage]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CompleteMessage]
GO
/****** Object:  StoredProcedure [dbo].[CompleteEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CompleteEntry]
GO
/****** Object:  StoredProcedure [dbo].[CleanQueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
DROP PROCEDURE [dbo].[CleanQueueEntry]
GO
/****** Object:  StoredProcedure [dbo].[CleanQueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CleanQueueEntry]
    @returnCode int output,
    @count int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        
        DECLARE @EntriesToBeDeletedTable TABLE(
            [Id] bigint PRIMARY KEY,
            [MessageId] bigint);

        BEGIN TRY
            INSERT INTO @EntriesToBeDeletedTable
            SELECT TOP (100)
                Id,
                MessageId
            FROM QueueEntry WITH (NOLOCK)
            WHERE
                StateId >= 3 OR
                ExpirationTime < SYSUTCDATETIME()
                    
            DELETE FROM MessageFragment WITH (ROWLOCK)
            WHERE MessageId IN
                (SELECT MessageId FROM @EntriesToBeDeletedTable)
            
            DELETE FROM Message WITH (ROWLOCK)
            WHERE Id IN
                (SELECT MessageId FROM @EntriesToBeDeletedTable)

            DELETE FROM QueueEntry WITH (ROWLOCK)
            WHERE Id IN
                (SELECT Id FROM @EntriesToBeDeletedTable)

        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[CompleteEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CompleteEntry]
    @queueEntryId int,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (UPDLOCK)
            SET
                StateId = 3,
                DeliveryAttemptCount = DeliveryAttemptCount + 1,
                DeliveryTime = SYSUTCDATETIME()
            WHERE
                Id = @queueEntryId

        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[CompleteMessage]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CompleteMessage]
    @messageId int,
    @partCount int,
    @size int,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
 
        BEGIN TRY
            UPDATE Message
            WITH (UPDLOCK)
            SET
                PartCount = @partCount,
                Size = @size
            WHERE
                Id = @messageId

            UPDATE QueueEntry
            WITH (UPDLOCK)
            SET
                StateId = 1
            WHERE
                MessageId = @messageId AND
                StateId = 0
                
        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[CreateErrorMessage]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CreateErrorMessage]
    @messageId bigint,
    @routingHeader varbinary(max),
    @machineName nvarchar(255),
    @processId int,
    @appDomainId int,
    @deliveryAttemptCount int,
    @errorMsg nvarchar(max)
AS 
    BEGIN
        SET NOCOUNT ON;
        
        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()

        BEGIN TRY
            INSERT INTO ErrorMessage
            (
                MessageId,
                RoutingHeader,
                MachineName,
                ProcessId,
                AppDomainId,
                DeliveryAttemptCount,
                ErrorMessage,
                CreateTime
            )
            VALUES
            (
                @messageId,
                @routingHeader,
                @machineName,
                @processId,
                @appDomainId,
                @deliveryAttemptCount,
                @errorMsg,
                @currentTime
            )

        END TRY
        BEGIN CATCH

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[CreateMessage]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CreateMessage]
    @correlationId nvarchar(100),
    @messageVersion nvarchar(255),
    @action nvarchar(255),
    @receiveTime datetime2,
    @receiveAddress nvarchar(max),
    @returnCode int output,
    @messageId bigint output
AS 
    BEGIN
        SET NOCOUNT ON;

        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()

        SET @returnCode = 0
        BEGIN TRY
            INSERT INTO Message
            (
                CorrelationId,
                MessageVersion,
                Action,
                ReceiveTime,
                ReceiveAddress,
                CreateTime
            )
            VALUES
            (
                @correlationId,
                @messageVersion,
                @action,
                @receiveTime,
                @receiveAddress,
                @currentTime
            )
            SET @messageId = SCOPE_IDENTITY()
            
        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[CreateMessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[CreateMessageFragment]
    @messageId bigint,
    @partIndex int,
    @body varbinary(max),
    @size int,
    @returnCode int output,
    @messageFragmentId int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        BEGIN TRY
            INSERT INTO MessageFragment
            (
                MessageId,
                PartIndex,
                Body,
                Size
            )
            VALUES
            (
                @messageId,
                @partIndex,
                @body,
                @size
            )
            SET @messageFragmentId = SCOPE_IDENTITY()
        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[DeleteMessage]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[DeleteMessage]
    @messageId bigint,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        BEGIN TRY
            DELETE FROM MessageFragment WITH (ROWLOCK)
            WHERE MessageId = @messageId
            
            DELETE FROM Message WITH (ROWLOCK)
            WHERE Id = @messageId
        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[DequeueEntries]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[DequeueEntries]
    @count int,
    @machineName nvarchar(100),
    @processId int,
    @appDomainId int,
    @processorId nvarchar(100),
    @returnCode int output,
    @batchId uniqueidentifier output
AS 
    BEGIN
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED

        DECLARE @currentTime datetime2
        SET @currentTime = SYSUTCDATETIME()
        SET @batchId = NEWID()
        SET @returnCode = 0
        BEGIN TRY
            BEGIN TRAN T1
            
            UPDATE TOP(@count) QueueEntry
            -- WITH (ROWLOCK, XLOCK, HOLDLOCK)
            WITH (UPDLOCK, ROWLOCK, READPAST)
            SET
                StateId = 2,
                MachineName = @machineName,
                ProcessId = @processId,
                ProcessorId = @processorId,
                BatchId = @batchId,
                DequeueTime = @currentTime,
                DequeueAttemptCount = DequeueAttemptCount + 1
                WHERE
                    StateId = 1 AND
                    BatchId IS NULL AND
                    DeliveryAttemptCount < MaxDeliveryAttemptCount AND
                    (ExpirationTime IS NULL OR
                     ExpirationTime < @currentTime) AND                   
                    ((IsResponse = 0) OR
                     (IsResponse = 1 AND
                      ReceiverMachineName = @machineName AND
                      ReceiverProcessId = @processId AND
                      ReceiverAppDomainId = @appDomainId))

            SELECT
                qe.[Id],
                qe.MessageId,
                qe.RoutingHeader,
                qe.Priority,
                qe.OrderedDelivery,
                qe.ReceiverMachineName,
                qe.ReceiverProcessId,
                qe.ReceiverAppDomainId,
                qe.EnqueueTime,
                qe.ExpirationTime,
                qe.StateId,
                qe.MachineName,
                qe.ProcessId,
                qe.ProcessorId,
                qe.BatchId,
                qe.DequeueTime,
                qe.DequeueAttemptCount,
                qe.DeliveryAttemptCount,
                qe.IsResponse,
                m.PartCount,
                m.Size,
                m.CorrelationId,
                m.MessageVersion,
                m.Action,
                m.ReceiveTime,
                m.ReceiveAddress,
                m.CreateTime
            FROM QueueEntry qe WITH (NOLOCK), Message m WITH (NOLOCK)
            WHERE
                qe.BatchId = @batchId AND
                qe.MessageId = m.Id
            ORDER BY
                [Priority] DESC, [Id] ASC

            COMMIT TRAN T1
                
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN T1

            SET @batchId = NULL
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[EnqueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[EnqueueEntry]
    @messageId bigint,
    @routingHeader varbinary(max),
    @priority int,
    @orderedDelivery bit,
    @receiverMachineName nvarchar(255),
    @receiverProcessId int,
    @receiverAppDomainId int,
    @messageValidityDurationSecs bigint = NULL,
    @deliveryAttemptIntervalSecs bigint,
    @maxDeliveryAttemptCount int,
    @isResponse bit,
    @returnCode int output,
    @queueEntryId int output
AS 
    BEGIN
        SET NOCOUNT ON;
        SET DEADLOCK_PRIORITY HIGH
        
        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()
        DECLARE @expirationTime datetime2(7) = DATEADD(second, @messageValidityDurationSecs, @currentTime)

        SET @returnCode = 0
        BEGIN TRY
            INSERT INTO QueueEntry
            (
                MessageId,
                RoutingHeader,
                Priority,
                OrderedDelivery,
                ReceiverMachineName,
                ReceiverProcessId,
                ReceiverAppDomainId,
                EnqueueTime,
                ExpirationTime,
                StateId,
                DequeueAttemptCount,
                DeliveryAttemptCount,
                DeliveryAttemptIntervalSecs,
                MaxDeliveryAttemptCount,
                IsResponse
            )
            VALUES
            (
                @messageId,
                @routingHeader,
                @priority,
                @orderedDelivery,
                @receiverMachineName,
                @receiverProcessId,
                @receiverAppDomainId,
                @currentTime,
                @expirationTime,
                0,
                0,
                0,
                @deliveryAttemptIntervalSecs,
                @maxDeliveryAttemptCount,
                @isResponse
            )
            SET @queueEntryId = SCOPE_IDENTITY()

        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[FaultEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[FaultEntry]
    @queueEntryId int,
    @errorMsg nvarchar(max),
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (UPDLOCK)
            SET
                StateId = 4,
                --MachineName = NULL,
                --ProcessId = NULL,
                --ProcessorId = NULL,
                BatchId = NULL,
                DeliveryAttemptCount = DeliveryAttemptCount + 1,
                DeliveryTime = NULL,
                ErrorMessage = @errorMsg
            WHERE
                Id = @queueEntryId

        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  StoredProcedure [dbo].[GetMessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[GetMessageFragment]
    @messageId bigint,
    @partIndex int
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT
            MessageId,
            PartIndex,
            Body,
            Size
        FROM MessageFragment WITH (NOLOCK)
        WHERE
            MessageId = @messageId AND
            PartIndex = @partIndex
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
   END CATCH
    
END

GO
/****** Object:  StoredProcedure [dbo].[RollbackEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[RollbackEntry]
    @queueEntryId int,
    @errorMsg nvarchar(max),
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

        SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (ROWLOCK)
            SET
                StateId = 1,
                MachineName = NULL,
                ProcessId = NULL,
                ProcessorId = NULL,
                BatchId = NULL,
                DeliveryAttemptCount = DeliveryAttemptCount + 1,
                DeliveryTime = NULL,
                ErrorMessage = @errorMsg
            WHERE
                Id = @queueEntryId

        END TRY
        BEGIN CATCH
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END

GO
/****** Object:  Table [dbo].[ErrorMessage]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorMessage](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [MessageId] [bigint] NOT NULL,
    [RoutingHeader] [varbinary](max) NULL,
    [MachineName] [nvarchar](255) NULL,
    [ProcessId] [int] NULL,
    [AppDomainId] [nvarchar](100) NULL,
    [DeliveryAttemptCount] [nchar](10) NULL,
    [ErrorMessage] [nvarchar](max) NOT NULL,
    [CreateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_ErrorMessage] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Message]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Message](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [PartCount] [int] NULL,
    [Size] [int] NULL,
    [CorrelationId] [nvarchar](100) NOT NULL,
    [MessageVersion] [nvarchar](255) NOT NULL,
    [Action] [nvarchar](255) NOT NULL,
    [ReceiveTime] [datetime2](7) NOT NULL,
    [ReceiveAddress] [nvarchar](max) NOT NULL,
    [CreateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MessageFragment]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MessageFragment](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [MessageId] [bigint] NOT NULL,
    [PartIndex] [int] NOT NULL,
    [Body] [varbinary](max) NOT NULL,
    [Size] [int] NOT NULL,
 CONSTRAINT [PK_MessageFragment] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MessageProcessingState]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MessageProcessingState](
    [Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_MessageProcessingState] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[QueueEntry]    Script Date: 27-4-2014 16:45:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueueEntry](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [MessageId] [bigint] NOT NULL,
    [RoutingHeader] [varbinary](max) NOT NULL,
    [Priority] [int] NOT NULL,
    [OrderedDelivery] [bit] NOT NULL,
    [ReceiverMachineName] [nvarchar](255) NOT NULL,
    [ReceiverProcessId] [int] NOT NULL,
    [ReceiverAppDomainId] [int] NOT NULL,
    [EnqueueTime] [datetime2](7) NOT NULL,
    [ExpirationTime] [datetime2](7) NULL,
    [StateId] [tinyint] NOT NULL,
    [MachineName] [nvarchar](255) NULL,
    [ProcessId] [int] NULL,
    [ProcessorId] [nvarchar](100) NULL,
    [BatchId] [uniqueidentifier] NULL,
    [DequeueTime] [datetime2](7) NULL,
    [DequeueAttemptCount] [int] NOT NULL,
    [DeliveryTime] [datetime2](7) NULL,
    [DeliveryAttemptCount] [int] NOT NULL,
    [DeliveryAttemptIntervalSecs] [bigint] NOT NULL,
    [MaxDeliveryAttemptCount] [int] NOT NULL,
    [IsResponse] [bit] NOT NULL,
    [ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_Delivery] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[QueueEntry] SET (LOCK_ESCALATION = DISABLE)
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (0, N'Receiving')
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (1, N'Pending')
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (2, N'Delivering')
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (3, N'Delivered')
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (4, N'Faulted')
GO
INSERT [dbo].[MessageProcessingState] ([Id], [Name]) VALUES (5, N'Terminated')
GO
/****** Object:  Index [IX_MessageFragment_MessageId]    Script Date: 27-4-2014 16:45:28 ******/
CREATE NONCLUSTERED INDEX [IX_MessageFragment_MessageId] ON [dbo].[MessageFragment]
(
    [MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_QueueEntry_BatchId]    Script Date: 27-4-2014 16:45:28 ******/
CREATE NONCLUSTERED INDEX [IX_QueueEntry_BatchId] ON [dbo].[QueueEntry]
(
    [BatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_QueueEntry_MessageId]    Script Date: 27-4-2014 16:45:28 ******/
CREATE NONCLUSTERED INDEX [IX_QueueEntry_MessageId] ON [dbo].[QueueEntry]
(
    [MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ErrorMessage]  WITH CHECK ADD  CONSTRAINT [FK_ErrorMessage_Message] FOREIGN KEY([MessageId])
REFERENCES [dbo].[Message] ([Id])
GO
ALTER TABLE [dbo].[ErrorMessage] CHECK CONSTRAINT [FK_ErrorMessage_Message]
GO
ALTER TABLE [dbo].[MessageFragment]  WITH CHECK ADD  CONSTRAINT [FK_MessageFragment_Message] FOREIGN KEY([MessageId])
REFERENCES [dbo].[Message] ([Id])
GO
ALTER TABLE [dbo].[MessageFragment] CHECK CONSTRAINT [FK_MessageFragment_Message]
GO
ALTER TABLE [dbo].[QueueEntry]  WITH NOCHECK ADD  CONSTRAINT [FK_QueueEntry_Message] FOREIGN KEY([MessageId])
REFERENCES [dbo].[Message] ([Id])
GO
ALTER TABLE [dbo].[QueueEntry] CHECK CONSTRAINT [FK_QueueEntry_Message]
GO
ALTER TABLE [dbo].[QueueEntry]  WITH CHECK ADD  CONSTRAINT [FK_QueueEntry_MessageProcessingState1] FOREIGN KEY([StateId])
REFERENCES [dbo].[MessageProcessingState] ([Id])
GO
ALTER TABLE [dbo].[QueueEntry] CHECK CONSTRAINT [FK_QueueEntry_MessageProcessingState1]
GO
